package Action;

import java.io.File;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import javax.servlet.http.HttpServletRequest;

import Utils.Common;
import Utils.GenUtils;
import Utils.XmlReplace;


import Bean.TopicBean;

public class Topic{
	public boolean insertTopic(TopicBean bean, Connection conn ){
		String sql = "INSERT INTO topic (TopicId, TopicName, Thumbnail, CatId, DatePublished) " +
				"VALUES (?,?,?,?,?)";
		PreparedStatement stmt = null;
		try{
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, bean.getTopicId());
			stmt.setString(2, bean.getTopicName());
			stmt.setString(3, bean.getThumbnail());
			stmt.setString(4, bean.getCatId());
			stmt.setString(5, bean.getDatePublished());
			stmt.executeUpdate();
			return true;
		}catch (Exception e) {
			e.printStackTrace();
			return false;
		}finally{
			if(stmt!=null){try{stmt.close();}catch (Exception e) {}}
		}
	}
	public boolean updateTopic(TopicBean bean, Connection conn ){
		String sql = "UPDATE  topic SET TopicName=?, Thumbnail=?, CatId=?, DatePublished=? WHERE TopicId=? ";
				
		PreparedStatement stmt = null;
		try{
			stmt = conn.prepareStatement(sql);
			stmt.setString(5, bean.getTopicId());
			stmt.setString(1, bean.getTopicName());
			stmt.setString(2, bean.getThumbnail());
			stmt.setString(3, bean.getCatId());
			stmt.setString(4, bean.getDatePublished());
			stmt.executeUpdate();
			return true;
		}catch (Exception e) {
			e.printStackTrace();
			return false;
		}finally{
			if(stmt!=null){try{stmt.close();}catch (Exception e) {}}
		}
	}
	
	public String deleteTopic(HttpServletRequest req, Connection conn ){
		String topicId = req.getParameter("topicId");
		String sql = "SELECT * FROM topic WHERE TopicId=?";
		PreparedStatement stmt = null;
		ResultSet rs = null;

		try{
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, topicId);
			rs = stmt.executeQuery();
			if(rs.next()){
				String thumbnail=rs.getString("Thumbnail");
				String folder = GenUtils.genPathByAccountId(topicId);
				File f;
				if(thumbnail!=null && !thumbnail.equals("")){
					f = new File(folder+"/"+thumbnail);
					if(f.exists()) f.delete();
				}
				stmt.close();
				sql = "DELETE FROM topic WHERE TopicId=? ";
				stmt = conn.prepareStatement(sql);
				stmt.setString(1, topicId);
				if(stmt.executeUpdate()>0){
					stmt.close();
					sql = "DELETE FROM topic_news WHERE NewsId=?";
					stmt = conn.prepareStatement(sql);
					stmt.setString(1, topicId);
					stmt.executeUpdate();
				}
			}
			return Common.makeSuccessXML();
		}catch (Exception e) {
			e.printStackTrace();
			return Common.makeErrorXML("Error");
		}finally{
			if(rs!=null){try{rs.close();}catch (Exception e) {}}
			if(stmt!=null){try{stmt.close();}catch (Exception e) {}}
		}
	}
	public String listNewsInTopic(HttpServletRequest req, Connection conn){
		String topicId = req.getParameter("topicId");
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String xml = "";
		try{
			String sql = "SELECT * FROM news INNER JOIN topic_news ON topic_news.NewsId=news.NewsId WHERE TopicId=? ORDER BY DatePublished DESC";
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, topicId);
			rs = stmt.executeQuery();
			while(rs.next()){
				xml += "<news newsid=\"" + rs.getString("NewsId")
						+ "\" title=\""
						+ XmlReplace.escape(rs.getString("Title"))
						+ "\"  description=\""
						+ XmlReplace.escape(rs.getString("Summary"))
						+ "\" " 
						+ " thumbnail=\""
						+ XmlReplace.escape(rs.getString("Thumbnail"))
						+ "\" " 
						+ " poster=\""
						+ XmlReplace.escape(rs.getString("Poster"))
						+ "\" " 
						+ "createddate=\""
						+ XmlReplace.escape(rs.getString("DatePublished"))
						+ "\"/>";
			}
		}catch (Exception e) {
			e.printStackTrace();
			return Common.makeErrorXML("Error");
		}finally{
			if(rs!=null){try{rs.close();}catch (Exception e) {}}
			if(stmt!=null){try{stmt.close();}catch (Exception e) {}}
		}
		return Common.makeSuccessXML(xml);
	}
	
	public String addNewsToTopic(HttpServletRequest req, Connection conn){
		String newsId = req.getParameter("newsId");
		String topicId = req.getParameter("topicId");
		
		PreparedStatement stmt = null;
		try{
			String sql = "INSERT INTO topic_news (TopicId,NewsId) VALUES (?,?)";
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, topicId);
			stmt.setString(2, newsId);
			stmt.executeUpdate();
			return Common.makeSuccessXML();
		}catch (Exception e) {
//			e.printStackTrace();
			return Common.makeErrorXML("Error");
		}finally{
			if(stmt!=null){try{stmt.close();}catch (Exception e) {}}
		}
	}
	public String deleteNewsToTopic(HttpServletRequest req, Connection conn){
		PreparedStatement stmt = null;
		String newsId = req.getParameter("newsId");
		String topicId = req.getParameter("topicId");
		try{
			String sql = "DELETE FROM topic_news WHERE NewsId=? AND TopicId=?";
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, newsId);
			stmt.setString(2, topicId);
			stmt.executeUpdate();
			return Common.makeSuccessXML();
		}catch (Exception e) {
			e.printStackTrace();
			return Common.makeErrorXML("Error");
		}finally{
			if(stmt!=null){try{stmt.close();}catch (Exception e) {}}
		}
	}
	
	public String listTopic(Connection conn){
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String xml = "";
		try{
			String sql = "SELECT * FROM topic WHERE Status=1 ORDER BY DatePublished DESC";
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();
			while(rs.next()){
				TopicBean bean = new TopicBean();
				bean.setTopicId(rs.getString("TopicId"));
				bean.setTopicName(rs.getString("TopicName"));
				bean.setDatePublished(rs.getString("DatePublished"));
				bean.setThumbnail(rs.getString("Thumbnail"));
				xml+=bean.makeXML();
			}
		}catch (Exception e) {
			e.printStackTrace();
			return Common.makeErrorXML("Error");
		}finally{
			if(rs!=null){try{rs.close();}catch (Exception e) {}}
			if(stmt!=null){try{stmt.close();}catch (Exception e) {}}
		}
		return Common.makeSuccessXML(xml);
	}
	public String topicDetail(HttpServletRequest req,Connection conn){
		String topicId = req.getParameter("topicId");
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String xml = "";
		try{
			String sql = "SELECT * FROM topic WHERE topicId=?";
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, topicId);
			rs = stmt.executeQuery();
			if(rs.next()){
				TopicBean bean = new TopicBean();
				bean.setTopicId(rs.getString("TopicId"));
				bean.setTopicName(rs.getString("TopicName"));
				bean.setDatePublished(rs.getString("DatePublished"));
				bean.setThumbnail(rs.getString("Thumbnail"));
				bean.setCatId(rs.getString("CatId"));
				xml=bean.makeXML();
			}
		}catch (Exception e) {
			e.printStackTrace();
			return Common.makeErrorXML("Error");
		}finally{
			if(rs!=null){try{rs.close();}catch (Exception e) {}}
			if(stmt!=null){try{stmt.close();}catch (Exception e) {}}
		}
		return Common.makeSuccessXML(xml);
	}
	public String updateStatusTopic(HttpServletRequest req, Connection conn ){
		String topicId = req.getParameter("topicId");
		String status = req.getParameter("Status");
		String sql = "UPDATE topic SET Status=? WHERE TopicId=?";
		PreparedStatement stmt = null;
		ResultSet rs = null;

		try{
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, status);
			stmt.setString(2, topicId);
			stmt.executeUpdate();
			return Common.makeSuccessXML();
		}catch (Exception e) {
			e.printStackTrace();
			return Common.makeErrorXML("Error");
		}finally{
			if(rs!=null){try{rs.close();}catch (Exception e) {}}
			if(stmt!=null){try{stmt.close();}catch (Exception e) {}}
		}
	}
}